Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# REPLCHARS

The REPLCHARS function replaces one or more characters in a text expression.

Tip: When you are using a multibyte character set, you can use the REPLBYTES function instead of the REPLCHARS function. Also, you can use the CHANGECHARS function to change one or more occurrences of a specified string in a text value to another string.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

- When all arguments are TEXT values, the return value is TEXT.

- When all arguments are NTEXT values, the return value is NTEXT.

- When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

Syntax

REPLCHARS(text-expression characters [start])

Arguments

text-expression

  The expression in which characters are to be replaced. When the characters to be replaced from text-expression contain embedded line breaks, these breaks are removed. Other line breaks are preserved. Removed line breaks are not counted toward the total number of characters replaced. Line breaks in the replacement expression are retained in the output of REPLCHARS, but are likewise not counted.

characters

  A text expression that contains one or more characters that will replace existing characters in text-expression.

start

  An `INTEGER` that represents the character position at which to begin replacing characters. The position of the first character in text-expression is `1`. When you omit this argument, REPLCHARS starts with the first character. REPLCHARS replaces as many characters of text-expression as are required for the specified new characters. When the value of start is greater than the length of text-expression, REPLCHARS simply returns text-expression.

Examples

Example 20-24 Replacing Text Characters

This example shows how to replace a portion of the text value `Hello there, Joe`.

The statement

    SHOW REPLCHARS('Hello there, Joe', 'Jane', 14)

produces the following output.

    Hello there, Jane

Example 20-25 How REPLCHARS Handles Line Breaks

This example shows how REPLCHARS preserves but ignores line breaks.

    var1 = JOINLINES('Hello' 'there' 'Joe')
    var2 = JOINLINES('Hi' 'Jane')

The statement

    show REPLCHARS(var1 var2)

produces the following output.

    Hi
    Janehere
    Joe

REPLCHARS has replaced the first 6 characters of `var1` (`Hellot` of `HellothereJoe`) with the 6 characters of `var2` (`HiJane`). It has preserved the line breaks following `Hi` (from `var2`) and `there` (from `var1`).

To replace all 13 characters in `var1`, you must specify 13 replacement characters; for example, you can add 7 spaces after `Jane`.

    var2 = JOINLINES('Hi' 'Jane       ')

The statement

    SHOW REPLCHARS(var1 var2)

produces the following output.

    Hi
    Jane